/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.model;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.db.SqlManager;
import com.exception.DataException;
import com.javabeans.Aula;
import com.javabeans.Facultad;
import java.util.Locale;
/**
 * @author Administrador
 */
public class AdminAula implements AulaManager{
    private Connection c;

    public AdminAula(Connection c){
        this.c = c;
    }
    
    public int getIdAula() throws DataException {
        int res = 0;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "SELECT IFNULL(MAX(IDAULA)+1,1) AS ID FROM AULA";
        try{
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            rs.next();
            res = rs.getInt("ID");
        }catch(SQLException e){
            throw new DataException(" AdminAula -> getIdAula() "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return res;
    }
    
    public void saveAula(Aula a) throws DataException{
        PreparedStatement preSt = null;
        try{
            String query = "INSERT INTO AULA VALUES";
            query += "("+a.getIdAula();
            query += ",'"+a.getNro().toUpperCase();
            query += "','"+a.getDescripcion();
            query += "',"+a.isActivo()+","+a.getIdFacultad()+")";            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException e){
            throw new DataException(" AdminAula -> saveAula(Aula a) SQLException = "+e.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    public Aula getAula(int idAula)throws DataException{
        Aula a = new Aula();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT A.IDAULA, A.NRO, A.DESCRIPCION, A.ACTIVO";
            query += ", A.IDFACULTAD, F.NOMBRE, F.SIGLA";
            query += " FROM AULA A";
            query += " INNER JOIN FACULTAD F ON A.IDFACULTAD=F.IDFACULTAD";
            query += " WHERE IDAULA = "+idAula;
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next()){
                a.setIdAula(rs.getInt("A.IDAULA"));
                a.setNro(rs.getString("A.NRO"));
                a.setDescripcion(rs.getString("A.DESCRIPCION"));
                a.setActivo(rs.getBoolean("A.ACTIVO"));
                a.setIdFacultad(rs.getInt("A.IDFACULTAD"));
                Facultad f = new Facultad();
                f.setNombre(rs.getString("F.NOMBRE"));
                f.setSigla(rs.getString("F.SIGLA"));
                a.setFacultad(f);
            }
        }catch(SQLException e){
            throw new DataException(" AdminAula -> getAula(int idAula) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return a;
    }
    
    public List<Aula> getAulas()throws DataException{
        List<Aula> aulas = new ArrayList<Aula>();
        try{
            aulas = this.getAulasGeneric(0,true);
        }catch(SQLException e){
            throw new DataException(" AdminAula -> getAulasGeneric(0,true) e = "+e.getMessage());
        }
        return aulas;
    }
    
    public List<Aula> getAulasInactivos()throws DataException{
        List<Aula> aulas = new ArrayList<Aula>();
        try{
            aulas = this.getAulasGeneric(0,false);
        }catch(SQLException e){
            throw new DataException(" AdminAula -> getAulasGeneric(0,true) e = "+e.getMessage());
        }
        return aulas;
    }
    
    public List<Aula> getAulasPorFacultad(int idFacultad)throws DataException{
        List<Aula> aulas = new ArrayList<Aula>();
        try{
            aulas = this.getAulasGeneric(idFacultad,true);
        }catch(SQLException e){
            throw new DataException(" AdminAula -> getAulasGeneric(idFacultad,false) e = "+e.getMessage());
        }
        return aulas;
    }
    
    public List<Aula> getAulasGeneric(int idFacultad, boolean activo)throws DataException{
        List<Aula> aulas = new ArrayList<Aula>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT A.IDAULA, A.NRO, A.DESCRIPCION";
            query += ", A.IDFACULTAD, F.NOMBRE, F.SIGLA";
            query += " FROM AULA A";
            query += " INNER JOIN FACULTAD F ON A.IDFACULTAD=F.IDFACULTAD";
            query += " WHERE";
            if(idFacultad != 0){
                if(activo)
                    query += " A.IDFACULTAD = "+idFacultad+" AND A.ACTIVO=1";
                else
                    query += " A.IDFACULTAD = "+idFacultad+" AND A.ACTIVO=0";
            }else if(activo){
                    query += " A.ACTIVO = 1";
                }else
                    query += " A.ACTIVO = 0";
            if(idFacultad != 0)
                query += " ORDER BY A.NRO";
            else
                query += " ORDER BY F.NOMBRE, A.NRO";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Aula a = new Aula();
                a.setIdAula(rs.getInt("IDAULA"));
                a.setNro(rs.getString("NRO"));
                a.setDescripcion(rs.getString("DESCRIPCION"));
                a.setActivo(true);
                a.setIdFacultad(rs.getInt("A.IDFACULTAD"));
                Facultad f = new Facultad();
                f.setNombre(rs.getString("F.NOMBRE"));
                f.setSigla(rs.getString("F.SIGLA"));
                a.setFacultad(f);
                aulas.add(a);
            }
        }catch(SQLException e){
            throw new DataException(" -> getAulasGeneric() e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return aulas;
    }
    
    public void updateAula(Aula a) throws DataException {
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE AULA SET NRO = '"+a.getNro();
            query += "', DESCRIPCION = '"+a.getDescripcion();
            query += "' WHERE IDAULA = "+a.getIdAula();            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException e){
            throw new DataException(" AdminAula -> updateAula(Aula a) DataException = "+e.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    public void darBajaAula(int idAula) throws DataException {
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE AULA SET ACTIVO = 0 WHERE IDAULA = "+idAula;            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException e){
            throw new DataException(" AdminAula -> darBajaAula(int idAula) DataException = "+e.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
}
